---
title: "SQL-PADR-01: Provider addresses for a location"
summary: Retrieve formatted mailing address details for a provider location
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PADR-01 Provider addresses for a location

Retrieves the address collection for a provider location, joined against the parent location
row for business-name and phone fallbacks, and pre-formats city/state/zip and phone/fax display
strings using `NVL`, `NVL2`, and string concatenation.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PADR-01 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProviderAddressCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ProviderLocationID` | Input | Decimal | N/A | `PROVIDER_LOCATION.PROVIDER_LOCATION_SEQ_NUM` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | SELECT | `PROVIDER_LOCATION_SEQ_NUM`, `BUSINESS_NAME`, `TOLL_FREE_PHONE_NUM`, `PHONE_AREA_CODE`, `PHONE_NUM`, `PHONE_NUM_EXT`, `TOLL_FREE_FAX_NUM`, `FAX_NUM`, `BUSINESS_FLAG` |
| [PROVIDER_ADDRESS](../database-tables.md#provider_address) | JOIN (LEFT OUTER) | `PROVIDER_ADDRESS_SEQ_NUM`, `EFF_DATE`, `EXP_DATE`, `ADDRESS_LINE1`-`4`, `FOREIGN_ADDRESS_FLAG`, `CITY_NAME`, `STATE_CODE`, `ZIP_CODE`, `ZIP_CODE_EXT`, `PROVIDER_ADDRESS_CODE`, `UPDATE_USER`, `UPDATE_DATE` |

## Sample SQL

```sql
-- Provider addresses for a location
SELECT
  pa.provider_address_seq_num,
  pa.eff_date,
  pa.exp_date,
  ppl.business_name,
  NVL(pa.address_line1, ' ') AS address_line1,
  pa.address_line2,
  pa.address_line3,
  pa.address_line4,
  NVL(pa.foreign_address_flag, 'N') AS foreign_address_flag,
  trim(pa.city_name) || ' ' || trim(pa.state_code) || ' ' || trim(pa.ZIP_CODE)
    || DECODE(pa.zip_code_ext, NULL, '', '-' || pa.zip_code_ext) AS addr_city_st,
  NVL(ppl.TOLL_FREE_PHONE_NUM,
      NVL2(ppl.PHONE_AREA_CODE, '(' || ppl.PHONE_AREA_CODE || ') ', '')
      || NVL2(ppl.PHONE_NUM,
              SUBSTR(ppl.phone_num, 1, 3) || '-' || SUBSTR(ppl.PHONE_NUM, -4), '')
      || NVL2(ppl.PHONE_NUM_EXT, 'x' || ppl.PHONE_NUM_EXT, '')) AS phone_num,
  NVL(ppl.toll_free_fax_num, ppl.fax_num) AS fax_num,
  NVL(pa.PROVIDER_ADDRESS_CODE, ' ') AS PROVIDER_ADDRESS_CODE,
  NVL(pa.CITY_NAME, ' ') AS CITY_NAME,
  pa.STATE_CODE,
  pa.ZIP_CODE,
  pa.ZIP_CODE_EXT,
  NVL(ppl.BUSINESS_FLAG, 'N') AS BUSINESS_FLAG,
  pa.update_user AS UpdateUser,
  pa.update_date AS UpdateDate
FROM PROVIDER_LOCATION ppl
  LEFT OUTER JOIN PROVIDER_ADDRESS pa
    ON pa.provider_location_seq_num = ppl.provider_location_seq_num
WHERE ppl.provider_location_seq_num = :ProviderLocationID
```

## Operations and processes
- [Load and select provider address](../operations/load-and-select-provider-address.md)

## Business rules
- [BR-PADR-001: Open-ended address expiration](../business-rules.md#br-padr-001-addresses-without-expiration-default-to-date-maxvalue)
- [BR-PADR-002: Address active date window](../business-rules.md#br-padr-002-address-active-on-date-determination)
- [BR-PADR-003: Primary address fallback chain](../business-rules.md#br-padr-003-primary-address-fallback-chain)
- [BR-PADR-004: Empty address instead of nothing](../business-rules.md#br-padr-004-empty-address-returned-when-none-match)
- [BR-PADR-005: Parent address preference](../business-rules.md#br-padr-005-parent-address-preferred-with-child-identity-retained)

## Sources
- SAIF.Business.View.Provider/clsProviderAddress.vb
- SAIF.Business.View.Provider/clsProviderAddressCollection.vb